﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class Message
    {
        private RuleApprove ruleApprove = new RuleApprove();
        private Absence absence = new Absence();
        private Employee employee = new Employee();
        private Common common = new Common();

        private DBManager dbManager = new DBManager(DataProvider.MySQL,
                                                    ConfigurationSettings.AppSettings["ConnectionString"]);

        #region Select

        public List<message> GetMessage(string message_seq, string from_emp_seq, string to_emp_seq
                                        , string from_message_seq, string task_approve_seq, string rule_approve_seq
                                        , bool isGetReference, bool isGetFromEmployee, bool isGetToEmployee,
                                        bool isGetFromMessage
                                        , bool isGetRuleApprove, bool isGetTaskApprove)
        {
            DataTable dtb;
            List<message> list = new List<message>();
            try
            {
                
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@message_seq", message_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(4, "@from_message_seq", from_message_seq);
                dbManager.AddParameters(5, "@task_approve_seq", task_approve_seq);
                dbManager.AddParameters(6, "@rule_approve_seq", rule_approve_seq);

                string strSQL = "select message.*, approval_task.ref_type_seq as rule_approve_type_seq from message LEFT JOIN approval_task ON message.approval_task_seq = approval_task.approval_task_seq " +
                                /*"left join rule_approve r on m.rule_approve_seq = r.rule_approve_seq " +
                                "left join rule_approve_type t on t.rule_approve_type_seq = r.rule_approve_type_seq " +
                                "left join rule_approve_group g on g.rule_approve_group_seq = t.rule_approve_group_seq " +*/
                                "where message.record_status <> @delete_status"
                                + (!string.IsNullOrWhiteSpace(message_seq) ? " and message.message_seq = @message_seq" : "")
                                + (!string.IsNullOrWhiteSpace(from_emp_seq) ? " and message.from_emp_seq = @from_emp_seq" : "")
                                + (!string.IsNullOrWhiteSpace(to_emp_seq) ? " and message.to_emp_seq = @to_emp_seq" : "")
                                +
                                (!string.IsNullOrWhiteSpace(from_message_seq)
                                     ? " and message.from_message_seq = @from_message_seq"
                                     : "")
                                +
                                (!string.IsNullOrWhiteSpace(task_approve_seq)
                                     ? " and message.task_approve_seq = @task_approve_seq"
                                     : "")
                                +
                                (!string.IsNullOrWhiteSpace(rule_approve_seq)
                                     ? " and message.rule_approve_seq = @rule_approve_seq"
                                     : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<message>) CollectionHelper.ConvertTo<message>(dtb);

                var employees = employee.GetHrmPsEmployeeLite();
                List<rule_approve> ruleApproves = new List<rule_approve>();


                if (isGetRuleApprove)
                {
                    ruleApproves = ruleApprove.GetRuleApprove(null, null, true, true, false);
                }
                foreach (message info in list)
                {

                    if (isGetFromEmployee && info.from_emp_seq != null)
                    {
                        //List<hrm_ps_employees> sub_list = employee.GetHrmPsEmployee(info.from_emp_seq.ToString()
                        //                                                            , null, null, null, null, null, null,
                        //                                                            null, true, false, false, false,
                        //                                                            false, false
                        //                                                            , true, false, true, true, false,
                        //                                                            false, true, false);

                        //info.from_hrm_ps_employees = sub_list.Count > 0 ? sub_list[0] : null;
                        info.from_hrm_ps_employees =
                            (from emp in employees where emp.emp_seq  == info.from_emp_seq select emp).FirstOrDefault();
                    }

                    if (isGetToEmployee && info.to_emp_seq != null)
                    {
                        //List<hrm_ps_employees> sub_list = employee.GetHrmPsEmployee(info.to_emp_seq.ToString()
                        //                                                            , null, null, null, null, null, null,
                        //                                                            null, true, false, false, false,
                        //                                                            false, false
                        //                                                            , true, false, true, true, false,
                        //                                                            false, true, false);

                        //info.to_hrm_ps_employees = sub_list.Count > 0 ? sub_list[0] : null;
                        info.from_hrm_ps_employees =
                            (from emp in employees where emp.emp_seq == info.to_emp_seq select emp).FirstOrDefault();
                    }

                    if (isGetFromMessage && info.from_message_seq != null)
                    {
                        List<message> sub_list = GetMessage(info.from_message_seq.ToString()
                                                            , null, null, null, null, null, false, false, false, false,
                                                            false, false);

                        info.from_message = sub_list.Count > 0 ? sub_list[0] : null;
                    }

                    if (isGetTaskApprove && info.approval_task_seq != null)
                    {
                        info.approval_task = new DAL.ApprovalTaskDal().GetApprovalTask(new approval_task {ApprovalTaskSeq = info.approval_task_seq.Value}, true).FirstOrDefault();
                        
                        //List<task_approve> sub_list = ruleApprove.GetRuleApprove(info.rule_approve_seq.ToString()
                        //    , null, false, false, false);

                        //info.task_approve = sub_list.Count > 0 ? sub_list[0] : null;
                    }

                    if (isGetRuleApprove && info.rule_approve_seq != null)
                    {
                        info.rule_approve = ruleApproves.FirstOrDefault(o => o.rule_approve_seq == info.rule_approve_seq); 
                        if(info.rule_approve != null && info.rule_approve.rule_approve_type != null)
                        {
                            info.rule_approve_type_name = info.rule_approve.rule_approve_type.rule_approve_type_name;
                            info.rule_approve_type_seq = info.rule_approve.rule_approve_type_seq.Value;
                        }
                        if (info.rule_approve != null && info.rule_approve.rule_approve_type != null && info.rule_approve.rule_approve_type.rule_approve_group != null)
                        {
                            info.rule_approve_group_name = info.rule_approve.rule_approve_type.rule_approve_group.rule_approve_group_name;
                            info.rule_approve_group_seq = info.rule_approve.rule_approve_type.rule_approve_group_seq.Value;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    //dbManager.Dispose();
                }
            }

            return list;
        }

        public List<message> GetMessageByEmpSeq(int? empSeq)
        { 
            List<message> list = new List<message>();
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus); 
                dbManager.AddParameters(1, "@to_emp_seq", empSeq);
                var strSQL = @"SELECT *,
rg.rule_approve_group_seq m_rule_approve_group_seq,
rg.rule_approve_group_name m_rule_approve_group_name,
rt.rule_approve_type_seq m_rule_approve_type_seq,
rt.rule_approve_type_name m_rule_approve_type_name,
e.emp_seq,
e.first_name, 
e.last_name, 
j.job_site_name,
j.job_site_seq
 FROM message m
LEFT JOIN approval_task a ON m.approval_task_seq = a.approval_task_seq
LEFT JOIN rule_approve ra ON ra.rule_approve_seq = a.approval_task_seq
LEFT JOIN rule_approve_type rt ON rt.rule_approve_type_seq = ra.rule_approve_type_seq
LEFT JOIN rule_approve_group rg ON rg.rule_approve_group_seq = rt.rule_approve_group_seq
LEFT JOIN hrm_ps_employees e ON m.from_emp_seq = e.emp_seq
LEFT JOIN hrm_mp_job_sites j ON e.working_job_site_seq = j.job_site_seq

WHERE m.record_status != @delete_status AND 
a.record_status != @delete_status";

                if (empSeq.HasValue)
                {
                    strSQL += @" AND 
( m.to_emp_seq = @to_emp_seq )";
                    DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    foreach (DataRow messageDataRow in dtb.Rows)
                    {
                        message eachMessage = new message();
                        eachMessage.message_seq = Convert.ToInt32(messageDataRow["message_seq"]);
                        eachMessage.message_no = messageDataRow["message_no"] as string;
                        eachMessage.message_date = messageDataRow["message_date"] as DateTime?;
                        eachMessage.message_title = messageDataRow["message_title"] as string;
                        eachMessage.message_descr = messageDataRow["message_descr"] as string;
                        eachMessage.from_emp_seq = messageDataRow["from_emp_seq"] as int?;
                        eachMessage.to_emp_seq = messageDataRow["to_emp_seq"] as int?;
                        eachMessage.from_message_seq = messageDataRow["from_message_seq"] as int?;
                        eachMessage.approval_task_seq = messageDataRow["approval_task_seq"] as int?;
                        eachMessage.rule_approve_seq = messageDataRow["rule_approve_seq"] as int?;
                        eachMessage.remarks = messageDataRow["remarks"] as string;
                        eachMessage.message_status = messageDataRow["message_status"] as string;
                        eachMessage.action_count = (messageDataRow["action_count"] as int?) ?? 0;

                        eachMessage.rule_approve_group_seq = (messageDataRow["m_rule_approve_group_seq"] as int?) ?? 0;
                        eachMessage.rule_approve_group_name = messageDataRow["m_rule_approve_group_name"] as string;
                        eachMessage.rule_approve_type_seq = (messageDataRow["m_rule_approve_type_seq"] as int?) ?? 0;
                        eachMessage.rule_approve_type_name = messageDataRow["m_rule_approve_type_name"] as string;

                        approval_task message_approval_task = new approval_task();
                        message_approval_task.ApprovalTaskSeq = (messageDataRow["approval_task_seq"] as int?) ?? 0;
                        message_approval_task.WfInstantId = (messageDataRow["wf_instant_id"] as Guid?) ?? Guid.Empty;
                        message_approval_task.RefSeq = (messageDataRow["ref_seq"] as int?) ?? 0;
                        message_approval_task.RefTypeSeq = (messageDataRow["ref_type_seq"] as int?) ?? 0;
                        message_approval_task.Title = messageDataRow["title"] as string;
                        message_approval_task.StartWhen = (messageDataRow["start_when"] as DateTime?) ?? DateTime.MinValue;
                        message_approval_task.StopWhen = (messageDataRow["stop_when"] as DateTime?) ?? DateTime.MinValue;
                        message_approval_task.CurrentStep = (messageDataRow["current_step"] as int?) ?? 0;
                        message_approval_task.Status = (TaskStatus) ((messageDataRow["task_status"] as int?) ?? 0);
                        message_approval_task.ActionCount = (messageDataRow["action_count"] as int?) ?? 0;

                        eachMessage.approval_task = message_approval_task;

                        Model.hrm_ps_employees fromEmployee = new hrm_ps_employees
                            {
                                emp_seq = messageDataRow["emp_seq"] as int? ?? 0,
                                first_name = messageDataRow["first_name"] as string,
                                last_name = messageDataRow["last_name"] as string,
                                working_job_site_seq = messageDataRow["job_site_seq"] as int? ?? 0,
                                working_job_site_text = messageDataRow["job_site_name"] as string,
                            };

                        eachMessage.from_hrm_ps_employees = fromEmployee;

                        list.Add(eachMessage);
                        //message_approval_task. = messageDataRow["rule_approve_seq"] as string;
                        //message_approval_task.CreatorEmp = messageDataRow["creator"] as string;
                        //message_approval_task.CurrentRuleApprove.ApproversEmployeeString = messageDataRow["current_approvers"] as string;
                        //message_approval_task.HistoriesCollection = messageDataRow["history"] as string;
                        //message_approval_task.Completed = messageDataRow["task_status_complete"] as string;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                }
                if(dbManager != null)
                {
                    dbManager.Dispose();
                }
            }

            return list;
        }

        #endregion

        #region Insert / Update / Delete

        public string InsertMessage(string message_no, Nullable<DateTime> message_date
                                    , string message_title, string message_descr
                                    , string from_emp_seq, string to_emp_seq, string from_message_seq
                                    , string task_approve_seq, string rule_approve_seq, string message_status
                                    , string remarks, string record_status, string created_by, int actionCount,
                                    string ref_seq = null
            )
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);
                dbManager.AddParameters(0, "@message_seq", null);
                dbManager.AddParameters(1, "@message_no", message_no);
                dbManager.AddParameters(2, "@message_date", message_date);
                dbManager.AddParameters(3, "@message_title", message_title);
                dbManager.AddParameters(4, "@message_descr", message_descr);
                dbManager.AddParameters(5, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(6, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(7, "@from_message_seq", from_message_seq);
                dbManager.AddParameters(8, "@approval_task_seq", task_approve_seq);
                dbManager.AddParameters(9, "@rule_approve_seq", rule_approve_seq);
                dbManager.AddParameters(10, "@message_status", message_status);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@record_status", record_status);
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);
                dbManager.AddParameters(15, "@ref_seq", ref_seq);
                dbManager.AddParameters(16, "@action_count", actionCount);

                string strSQL =
                    @"insert into message
(message_no, message_date
, message_title, message_descr
, from_emp_seq, to_emp_seq
, from_message_seq, approval_task_seq
, rule_approve_seq,remarks, message_status
, record_status, created_by, created_when, action_count)
values(@message_no, now()
,@message_title, @message_descr
, @from_emp_seq, @to_emp_seq
, @from_message_seq, @approval_task_seq
, @rule_approve_seq, @remarks, @message_status
, @record_status, @created_by, @created_when, @action_count)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from message";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
            return strSeq;
        }

        public void UpdateMessage(string message_seq, string message_no, Nullable<DateTime> message_date
                                  , string message_title, string message_descr
                                  , string from_emp_seq, string to_emp_seq, string from_message_seq
                                  , string task_approve_seq, string rule_approve_seq, string message_status
                                  , string remarks, string record_status, string updated_by, int actionCount,
                                  string ref_seq = null)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@message_seq", message_seq);
                dbManager.AddParameters(1, "@message_no", message_no);
                dbManager.AddParameters(2, "@message_date", message_date);
                dbManager.AddParameters(3, "@message_title", message_title);
                dbManager.AddParameters(4, "@message_descr", message_descr);
                dbManager.AddParameters(5, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(6, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(7, "@from_message_seq", from_message_seq);
                dbManager.AddParameters(8, "@task_approve_seq", task_approve_seq);
                dbManager.AddParameters(9, "@rule_approve_seq", rule_approve_seq);
                dbManager.AddParameters(10, "@message_status", message_status);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@record_status", record_status);
                dbManager.AddParameters(13, "@updated_by", updated_by);
                dbManager.AddParameters(14, "@updated_when", DateTime.Now);

                string strSQL =
                    @"update message
set message_no = @message_no
, message_date = @message_date
, message_title = @message_title
, message_descr = @message_descr
, from_emp_seq = @from_emp_seq
, to_emp_seq = @to_emp_seq
, from_message_seq = @from_message_seq
, task_approve_seq = @task_approve_seq
, rule_approve_seq = @rule_approve_seq
, message_status = @message_status
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where message_seq = @message_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void UpdateStatusMessage(string message_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@message_seq", message_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
                    @"update message
set record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where message_seq = @message_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void ReadMessage(string message_seq, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@message_seq", message_seq);
                dbManager.AddParameters(1, "@message_status", 0);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
                    @"update message
set message_status = @message_status
, updated_by = @updated_by
, updated_when = @updated_when
where message_seq = @message_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void WarnMessage(int  warn_count, string emp_seq, string updated_by)
        {
            if(warn_count == 0) return;

            if (string.IsNullOrWhiteSpace(emp_seq) || string.IsNullOrWhiteSpace(updated_by)) return;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@warn_count", warn_count); 
                dbManager.AddParameters(1, "@message_unwarn_status", 1);
                dbManager.AddParameters(2, "@message_warn_but_unread_status", 2);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);

                string strSQL = "select message_seq from message where to_emp_seq = @emp_seq and message_status = @message_unwarn_status limit @warn_count";
                DataTable dt1 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                
                if(dt1.Rows.Count > 0)
                {
                    List<string> message_seqs = (from DataRow dr in dt1.Rows select Convert.ToString(dr["message_seq"])).ToList();

                    strSQL = @"update message
                                set message_status = @message_warn_but_unread_status
                                , updated_by = @updated_by
                                , updated_when = @updated_when
                                where message_seq in (" + string.Join(",", message_seqs) + ")";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                
                } 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        #endregion

    }
}